package dataBaseInteraction;

/**
 * 
 * Clasa ce contine metodele pentru conectarea si deconectarea de la baza de date, precum si
 * metodele pentru inserari si interogari din baza de date
 * 
 * 
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class DataProcessing {

	public static String userName = "root";
    public static String password = "";
    public static String url = "jdbc:mysql://localhost/consum";

    /**
     * creaza o conexiune la baza de date
     */
	public static Connection connectToDB(String user, String pass, String db)
	{
		Connection conn = null;
		try {
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
			conn = DriverManager.getConnection (db, user, pass);
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        return conn;
	}
	
	/**
	 * returneaza un array de 2 element ce reprezinta valoarea de consum minim si maxin al unui
	 * aparat ce consuma curent
	 */
	public static ArrayList<Float> getCurrentConsumption(Connection conn,String tabel,String aparat,String stare)
	{
		ArrayList<Float> valori=new ArrayList<Float>();
		Statement s;
		ResultSet rs;
		try {
			s=conn.createStatement();
			String query;
			query="SELECT consum_min, consum_max FROM "+tabel+" WHERE aparat LIKE '"+aparat+"' AND stare LIKE '"+stare+"';";
			s.executeQuery (query);
	        rs = s.getResultSet();
	        while (rs.next())
	        {
	        	valori.add(rs.getFloat("consum_min"));
	        	valori.add(rs.getFloat("consum_max"));
	        }
	        rs.close();
	        s.close();
	        
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return valori;
	}
	/**
	 * returneaza 128 daca semnaleaza miscarea unui om, 0 in caz contrar
	 */
	public static int getMovement(Connection conn,String tabel,String stare)
	{
		int valoare=-1;
		Statement s;
		ResultSet rs;
		try {
			s=conn.createStatement();
			String query;
			query="SELECT valoare FROM "+tabel+" WHERE stare LIKE '"+stare+"';";
			s.executeQuery (query);
	        rs = s.getResultSet();
	        while (rs.next())
	        {
	        	valoare=rs.getInt("valoare");
	        }
	        rs.close();
	        s.close();
	        
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return valoare;
	}
	/**
	 * returneaza consumul de gaz
	 */
	public static float getGassConsumption(Connection conn, String table, String tip)
	{
		Statement s;
		ResultSet rs;
		float consum=-1;
		try {
			s=conn.createStatement();
			String query;
			query="SELECT valoare FROM "+table+" WHERE tip LIKE '"+tip+"'";
			s.executeQuery(query);
			rs=s.getResultSet();
			while (rs.next())
			{
				consum=rs.getFloat("valoare");
			}
			rs.close();
			s.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return consum;
	}
	/**
	 * returneaza consumul de apa in functie de tipul spalarii (maini, dus, dinti, fata, etc)
	 */
	public static float getWaterConsumption(Connection conn, String table, String tip_spalare)
	{
		Statement s;
		ResultSet rs;
		float consum=-1;
		try {
			s=conn.createStatement();
			String query;
			query="SELECT consum FROM "+table+" WHERE tip_spalare LIKE '"+tip_spalare+"'";
			s.executeQuery(query);
			rs=s.getResultSet();
			while (rs.next())
			{
				consum=rs.getFloat("consum");
			}
			rs.close();
			s.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return consum;
		
	}
	/**
	 * sterge inregistrarile dintr-un tabel
	 */
	public static void clearTable(Connection conn,String tabel)
	{
		Statement s;
		try {
			s=conn.createStatement();
			String query;
			query="TRUNCATE "+tabel;
			s.executeUpdate(query);
	        s.getResultSet();
	        s.close();
	        
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * insereaza inregistrari intr-un tabel
	 */
	public static void insetIntoTable(Connection conn,String tabel, String ora, String senzor, float valoare)
	{
		Statement s;
		try {
			s=conn.createStatement();
			String query;
			query="INSERT INTO "+tabel+" VALUES (NULL,'"+ora+"','"+senzor+"',"+valoare+");";
			s.executeUpdate(query);
	        s.getResultSet();
	        s.close();
	        
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 
	 * inchide conexiunea la baza de date
	 */
	
	public static void closeDB(Connection conn)
	{
		if (conn!= null)
		{
			try {
				conn.close ();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}
	
	/**
	 * returneaza o valoare aleatoare dintre 2 numere
	 */
	public static float getRandomValue(float x, float y)
	{
		return (float) (x+Math.random()*(y-x));
	}

}
